-- create an extended event session to capture lock escalation
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'LockEscalation')
    DROP EVENT SESSION LockEscalation ON SERVER;
GO

-- create the event session
CREATE EVENT SESSION LockEscalation ON SERVER 
ADD EVENT sqlserver.lock_escalation( 
	ACTION (sqlserver.sql_text,
			sqlserver.database_name,
			sqlserver.client_hostname,
			sqlserver.username,
			sqlserver.tsql_stack,
			sqlserver.server_instance_name,
			sqlserver.session_id) 
	)
ADD TARGET package0.event_file
(
	SET FILENAME = N'C:\Temp\LockEscalation.xel',
		MAX_FILE_SIZE = 200, MAX_ROLLOVER_FILES = 5)
	WITH (MAX_MEMORY = 4096 KB, MAX_DISPATCH_LATENCY = 30 SECONDS);
GO 

-- start the session
ALTER EVENT SESSION LockEscalation ON SERVER STATE = START;
GO

-- monitor the workload

-- stop the event session
ALTER EVENT SESSION LockEscalation ON SERVER STATE = STOP;
GO

-- query target data
SELECT
	n.value ('@name', 'nvarchar(50)') AS event_name,
	n.value ('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
	n.value ('(data[@name="escalation_cause"]/value)[1]', 'varchar(50)') 
		+ ' - ' + n.value ('(data[@name="escalation_cause"]/text)[1]', 'varchar(50)') AS escalation_cause,
	OBJECT_NAME(n.value ('(data[@name="object_id"]/value)[1]', 'int')) AS 'object_name',
	n.value ('(data[@name="hobt_lock_count"]/value)[1]', 'varchar(50)') AS hobt_lock_count,
	n.value ('(data[@name="escalated_lock_count"]/value)[1]', 'varchar(50)') AS escalated_lock_count,
	n.value ('(data[@name="mode"]/value)[1]', 'varchar(50)')
		+ ' - ' + n.value ('(data[@name="mode"]/text)[1]', 'varchar(50)') AS resource_mode,
	n.value ('(data[@name="resource_type"]/value)[1]', 'varchar(50)') 
		+ ' - ' + n.value ('(data[@name="resource_type"]/text)[1]', 'varchar(50)') AS resource_type
FROM ( 
	SELECT
		CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file 
	(N'C:\Temp\LockEscalation*.xel', NULL, NULL, NULL)
) AS tab
CROSS APPLY event_data.nodes('//event') AS q(n);

-- cleanup
DROP EVENT SESSION LockEscalation ON SERVER;
GO